package birs.controller.expanditure.task;

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import birs.model.expanditure.XPD001Model;
import birs.model.expanditure.XPD001ModelA;
import birs.util.ConvertDataType;
import birs.util.ResultSetMapper;

public class XPD001Task {
	
	Connection con = null;
	public XPD001Task(Connection con){
		this.con = con;
	}
	
	public void getExpanditure(HttpServletRequest request,HttpServletResponse response) throws SQLException, IOException{
		ResultSetMapper<XPD001Model> xpd001Model = new ResultSetMapper<XPD001Model>();
		List<XPD001Model> xpd001ModelList = xpd001Model.mapRersultSetToObject(getExpanditureSQL(), XPD001Model.class);
		
		String resultJson = ConvertDataType.getInstance().objectToJasonArray(xpd001ModelList);
		sendResponse(request,response,resultJson);
	}
	
	public void getFirstDrilldown(HttpServletRequest request,HttpServletResponse response) throws IOException, SQLException{
		ResultSetMapper<XPD001ModelA> xpd001ModelA = new ResultSetMapper<XPD001ModelA>();
		List<XPD001ModelA> xpd001ModelAList = xpd001ModelA.mapRersultSetToObject(getFirstDrilldownSQL(request.getParameter("stdPeriod"),request.getParameter("acadYear")), XPD001ModelA.class);
		
		String resultJson = ConvertDataType.getInstance().objectToJasonArray(xpd001ModelAList);
		sendResponse(request,response,resultJson);
	}
	
	private void sendResponse(HttpServletRequest request,HttpServletResponse response,String resultJson) throws IOException{
		response.setCharacterEncoding("utf-8");
		response.setContentType("application/json;charset=utf-8");
		PrintWriter out = response.getWriter();
		out.write(resultJson);
		out.close();
	}
	
	private ResultSet getExpanditureSQL() throws SQLException{
		String sql = "(SELECT SM.STUDYPERIOD, "
				+ " E.ACADYEAR, SUM(E.AMOUNT) AS AMOUNT "
				+ " FROM ENROLLFEE E, STUDENTMASTER SM "
				+ " WHERE SM.STUDYPERIOD = 1 "
				+ " AND E.ACADYEAR > 2550 "
				+ " AND SM.STUDENTID = E.STUDENTID "
				+ " GROUP BY SM.STUDYPERIOD,E.ACADYEAR) "
				
				+ " UNION ALL "
				
				+ " (SELECT SM.STUDYPERIOD,"
				+ " E.ACADYEAR, SUM(E.AMOUNT) AS AMOUNT "
				+ " FROM ENROLLFEE E, STUDENTMASTER SM "
				+ " WHERE SM.STUDYPERIOD = 2 "
				+ " AND E.ACADYEAR > 2550 "
				+ " AND SM.STUDENTID = E.STUDENTID "
				+ " GROUP BY SM.STUDYPERIOD,E.ACADYEAR ) "
				
				+ " UNION ALL "
				
				+ " (SELECT '3' AS STUDYPERIOD, "
				+ " E.ACADYEAR, SUM(E.AMOUNT) AS AMOUNT "
				+ " FROM ENROLLFEE E, STUDENTMASTER SM "
				+ " WHERE SM.STUDYPERIOD in (1,2) "
				+ " AND E.ACADYEAR  > 2550 "
				+ " AND SM.STUDENTID = E.STUDENTID "
				+ " GROUP BY E.ACADYEAR ) "
				+ " ORDER BY STUDYPERIOD,ACADYEAR";
		return getData(sql);
	}
	
	private ResultSet getFirstDrilldownSQL(String stdPeriod,String acadYear) throws SQLException{
		if(stdPeriod.equalsIgnoreCase("ภาคปกติ")){
			stdPeriod = "=1";
		}else if(stdPeriod.equalsIgnoreCase("ภาคพิเศษ")){
			stdPeriod = "=2";
		}else{
			stdPeriod = "IN (1,2)";
		}
		String sql = " 	SELECT E.ACADYEAR ,F.FACULTYNAME,	"
				+" 	  SUM(E.AMOUNT) AS AMOUNT	"
				+" 	FROM AVSREG.ENROLLFEE E,	"
				+" 	  AVSREG.STUDENTMASTER SM,	"
				+" 	  AVSREG.FACULTY F	"
				+" 	WHERE SM.STUDYPERIOD "+stdPeriod
				+" 	AND E.ACADYEAR       = "+acadYear
				+" 	AND E.STUDENTID      = SM.STUDENTID	"
				+" 	AND SM.FACULTYID     = F.FACULTYID	"
				+" 	GROUP BY E.ACADYEAR,F.FACULTYNAME "
				+" 	ORDER BY AMOUNT";
		return getData(sql);
	}
	
	private ResultSet getData(String sql) throws SQLException {
		ResultSet result = null;
		
		try {
			Statement statement = con.createStatement();
			result = statement.executeQuery(sql);
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;
	}
}
